using System;
using System.Diagnostics;
using System.Linq;
using System.Data;
using System.Data.Linq;
using System.Collections.Generic;
using System.Text;

namespace GuavaData
{
    public class SqlProvider : Provider
    {
        protected HashSet<string> fkWorkList;
        
        #region Fields
        protected Dictionary<string, List<string>> columnsPerTable;
        protected Dictionary<string, List<string>> keyColumnsPerTable;
        protected List<string> foreignKeys;
        protected int iCount = 0;
        protected List<string> triggers;
        protected bool foundFK = false;

        // Building foreign keys: data structures to remember where Tier 1/2 foreign keys point
        protected Dictionary<string, Dictionary<string, List<ForeignKey>>> ForeignKeyTable;

        public Stopwatch sw;
        public Stopwatch prov;
        #endregion

        public static string MSSQL_PROVIDER = "MSSQL";
        public static string MYSQL_PROVIDER = "MySQL";
        public static string ACCESS_PROVIDER = "Access";
        public static string ORACLE_PROVIDER = "Oracle";
        public static string POSTGRES_PROVIDER = "Postgres";
        private static string provider = MYSQL_PROVIDER;
        public static string Provider
        {
            get
            {
                return provider;
            }
            set
            {
                provider = value;
            }
        }

        #region ForeignKey helper functions
        protected string SerializeTableAndColumns(string tablename, List<string> columns)
        {
            //columns.Sort();
            return tablename + "@" + String.Join("$", columns.ToArray());
        }

        protected string SerializeConditions(List<string> column, List<Condition> eqcondition)
        {
            List<string> strings = new List<string>();
            for (int i = 0; i < column.Count; i++)
            {
                strings.Add(column[i] + eqcondition[i].ToString());
            }
            strings.Sort();
            return String.Join("|", strings.ToArray());
        }

        protected ForeignKey IsTier1FKSource(string tablename, List<string> columns)
        {
            return IsTier1FKSource(SerializeTableAndColumns(tablename, columns));
        }

        protected ForeignKey IsTier1FKSource(string key)
        {
            if (!ForeignKeyTable.ContainsKey(key))
                // Key does not exist yet
                return null;

            var conditionmap = ForeignKeyTable[key];

            // Assume that the conditionmap dictionary is not empty
            if (conditionmap.Count != 1)
                // More logic than just a single FK, so not Tier 1
                return null;

            // Since we know there is only one, separate the one that we have
            var fkconditions = ((from o in conditionmap
                                select o.Key).ToArray())[0];

            // Test to see if the list of conditions is empty - if not, not Tier 1
            if (fkconditions.Length != 0)
                return null;

            // Get the foreign key statements
            List<ForeignKey> fks = conditionmap[fkconditions];

            // If more than one fk statement for the condition set, not Tier 1
            if (fks.Count > 1)
                return null;
            ForeignKey fk = fks[0];

            // Test the target fk to see if the parent has conditions
            if (fk.ParentConditionColumns.Count != 0)
                return null;

            // Test the target fk to see if the parent columns cover the parent primary key
            if (fk.ParentColumns.Count != keyColumnsPerTable[fk.ParentTable].Count)
                return null;

            return fk;
        }

        protected List<ForeignKey> AggragateForeignKeys(string key)
        {
            List<ForeignKey> fkList = new List<ForeignKey>();
            var condFKMap = ForeignKeyTable[key];
            foreach (var condition in condFKMap.Keys)
                foreach (var fk in condFKMap[condition])
                    fkList.Add(fk);
            return fkList;
        }

        protected SqlText CreateTier2ForeignKey(string key)
        {
            SqlText sql = new SqlText();

            //trying to form this statement i think
            //CREATE TRIGGER name ON table INSTEAD OF INSERT AS INSERT INTO table
            //  SELECT * FROM inserted, [select UNION select] where join conditions

            //CREATE Trigger SurgicalHistory@id ON SurgicalHistory INSTEAD OF INSERT 
            //AS DECLARE @count tinyint select @count=count(*) from INSERTED i@ where not exists 
            //(SELECT id as id FROM Colonoscopy UNION SELECT id as id FROM EGD) AS UNIONED@SELECTS WHERE i.id=UNIONED@SELECTS.id)
            //IF(@count=0)
            //    insert into SurgicalHistory select * from INSERTED
            //ELSE
            //    raiserror('Foreign key violation');

            //Group foreignkeys for processing
            List<ForeignKey> fkList = AggragateForeignKeys(key);

            string childTable = fkList[0].ChildTable;

            //See if fk or trigger is already there if so drop it
            if (foreignKeys.Contains(key))
            {
                DBConnection.ExecuteNonQuery("ALTER TABLE " + childTable + " DROP CONSTRAINT " + key);
                foreignKeys.Remove(key);
            }
            if (triggers.Contains(key))
                DBConnection.ExecuteNonQuery("DROP TRIGGER " + key);
            else 
                triggers.Add(key);

            sql.Command.Append("CREATE Trigger ");
            sql.Command.Append(key);
            sql.Command.Append(" ON ");

            ForeignKey firstFK = (ForeignKey) fkList[0];

            List<string> selectCols = new List<string>();
            for (int i = 0; i < firstFK.ChildColumns.Count; i++)
                selectCols.Add("UNIONED@SELECTS."+firstFK.ChildColumns[i]);

            HashSet<string> parentTables = new HashSet<string>();
            for (int i = 0; i < fkList.Count; i++)
                parentTables.Add(fkList[i].ParentTable);

            sql.Command.Append(childTable);
            sql.Command.Append(" INSTEAD OF INSERT AS DECLARE @count int SELECT @count=count(*) FROM INSERTED I@ WHERE NOT EXISTS (SELECT ");
            sql.Command.Append(String.Join(", ", selectCols.ToArray()));
            sql.Command.Append(" FROM ");
            // +firstFK.ChildTable + ", " + String.Join(", ", parentTables.ToArray()) + ", ";



            List<string> selectList = new List<string>();
            List<string> fkConditions = new List<string>();

            //create selects from fks
            for (int i = 0; i < fkList.Count; i++)
            {
                List<string> colList = new List<string>();
                for (int j = 0; j < fkList[i].ParentColumns.Count; j++)
                    colList.Add(fkList[i].ParentColumns[j] + " as " + fkList[i].ChildColumns[j]);
                //create parent condtions
                List<string> parentConditions = new List<string>();
                for (int j = 0; j < fkList[i].ParentConditionColumns.Count; j++)
                    parentConditions.Add(fkList[i].ParentConditionColumns[i] + fkList[i].ParentConditions[i].ToString());

                selectList.Add("SELECT " + String.Join(", ", colList.ToArray()) + " FROM " + fkList[i].ParentTable +(parentConditions.Count>0 ? " WHERE " + String.Join(", ", parentConditions.ToArray()):""));

                //create condition strings while we're at it
                string childConditions = ChildConditionsFromFK(fkList[i]);
                if (!childConditions.Equals(""))
                    fkConditions.Add(childConditions);

            }

            List<string> joinConditions = new List<string>();
            //create join conditions

            sql.Command.Append("(");
            sql.Command.Append(String.Join(" UNION ", selectList.ToArray()));
            sql.Command.Append(") AS UNIONED@SELECTS");

            for (int i = 0; i < fkList[0].ChildColumns.Count; i++)
            {
                joinConditions.Add("I@." + fkList[0].ChildColumns[i] + "=UNIONED@SELECTS." + fkList[0].ChildColumns[i]);
            }
            sql.Command.Append(" WHERE ");
            sql.Command.Append(String.Join(" AND ", joinConditions.ToArray()));

            if (fkConditions.Count > 0)
            {
                sql.Command.Append(" AND (");
                sql.Command.Append(String.Join(" OR ", fkConditions.ToArray()));
                sql.Command.Append(")");
            }

            sql.Command.Append(")\n IF(@count=0)\n insert into ");
            sql.Command.Append(childTable);
            sql.Command.Append(" select * from INSERTED\n ELSE raiserror('Foreign key violation',18,1)");

            return sql;

        }

        
        protected string ChildConditionsFromFK(ForeignKey fk)
        {
            List<string> conditions = new List<string>();

            //add child conditions
            for (int i = 0; i < fk.ChildConditionColumns.Count; i++)
                conditions.Add("I@." + fk.ChildConditionColumns[i] + fk.ChildConditions[i].ToString());


            if(conditions.Count > 0)
                return "(" + String.Join(" AND ", conditions.ToArray()) + ")";

            return "";
        }

        protected SqlText CreateTier1ForeignKey(ForeignKey fk)
        {
            SqlText sql = new SqlText();
            string fkName = SerializeTableAndColumns(fk.ChildTable, fk.ChildColumns);

            // If fk already exists, punt
            if (foreignKeys.Contains(fkName))
                return null;
            // Create simply a foreign key constraint
            sql.Command.Append("ALTER TABLE ");
            sql.Command.Append(fk.ChildTable);
            sql.Command.Append(" ADD CONSTRAINT ");
            sql.Command.Append(fkName);
            sql.Command.Append(" FOREIGN KEY (");
            sql.Command.Append(String.Join(", ", fk.ChildColumns.ToArray()));
            sql.Command.Append(") REFERENCES ");
            sql.Command.Append(fk.ParentTable);
            sql.Command.Append(" (");
            sql.Command.Append(String.Join(", ", fk.ParentColumns.ToArray()));
            sql.Command.Append(")");
            sql.Command.Append(" ON DELETE CASCADE ON UPDATE CASCADE");

            foreignKeys.Add(fkName);


            return sql;

        }

        #endregion

        public bool hasChild()
        {
            return false;
        }

        public Cell getChild()
        {
            return null;
        }

        public SqlProvider()
        {
            keyColumnsPerTable = new Dictionary<string, List<string>>();
            ForeignKeyTable = new Dictionary<string, Dictionary<string, List<ForeignKey>>>();
            fkWorkList = new HashSet<string>();
            sw = new Stopwatch();
            prov = new Stopwatch();
        }

        protected void GetCurrentSchema()
        {
            
            columnsPerTable = new Dictionary<string, List<string>>();
            foreignKeys = new List<string>();
            triggers = new List<string>();
            DataTable cols = DBConnection.getColumns();
            DataTable fks = DBConnection.getForeignKeys();
            DataTable trigs = DBConnection.getTriggers();
            foreach (DataRow dr in cols.Rows)
            {
                string s = (string)dr["TABLE_NAME"];
                if (!columnsPerTable.ContainsKey(s))
                    columnsPerTable[s] = new List<string>();
                columnsPerTable[s].Add((string)dr["COLUMN_NAME"]);
            }
            foreach (DataRow dr in fks.Rows)
                foreignKeys.Add((string)dr["CONSTRAINT_NAME"]);

            foreach (DataRow dr in trigs.Rows)
                triggers.Add((string)dr["name"]);
        }

        #region Provider Members

        public virtual DataSet Process(Transaction t)
        {
            //if we are preseeding there is no need to process the query
            if (t.Contents.Count > 0)
            {
                if (t.Contents[0].root is Filter)
                {
                    Filter f = (Filter)t.Contents[0].root;
                    if (f.ConditionString.Contains("'-__-'"))
                    {
                        return null;
                    }
                }
            }


            if (this.columnsPerTable == null)
                GetCurrentSchema();
            
            foreach (Command c in t.Contents)
            {
                prov.Start();
                SqlText sql = c.root.Visit(this);
                prov.Stop();
                
                if (sql != null && !(c.root.isQuery))
                {
                    //System.Windows.Forms.MessageBox.Show(sql.ToString());
                    sw.Start();
                    DBConnection.ExecuteNonQuery(sql.ToString());
                    sw.Stop();
                }
                else if (sql != null)
                {
                    sw.Start();
                    var retval = DBConnection.ExecuteQuery(sql.ToString());
                    sw.Stop();
                    return retval;
                }
                
            }
            
            if (foundFK)
            {
                ProcessForeignKeys();
                foundFK = false;
            }
            return null;
        }

        protected void ProcessForeignKeys()
        {
            SqlText sql = null;
            foreach (var key in fkWorkList)
            {
                ForeignKey fk = IsTier1FKSource(key);
                if (fk != null)
                    sql = CreateTier1ForeignKey(fk);
                else
                {
                    sql = CreateTier2ForeignKey(key);
                }
                if (sql != null)
                {
                    sw.Start();
                    //DBConnection.ExecuteNonQuery(sql.ToString());
                    System.Console.WriteLine(sql.ToString());
                    sw.Stop();
                }
                sql = null;
            }

        }

        public SqlText Transform(AddColumn ac)
        {
            // Determine if the column should be added
            if (this.columnsPerTable[ac.Table].Contains(ac.Column))
            {
                return null;
            }

            SqlText sql = new SqlText();
            sql.Command.Append("ALTER TABLE ");
            sql.Command.Append(ac.Table);
            sql.Command.Append(" ADD ");
            sql.Command.Append(ac.Column);
            sql.Command.Append(" ");
            sql.Command.Append(ac.Domain.DBType);
            sql.Command.Append(" NULL");
            this.columnsPerTable[ac.Table].Add(ac.Column);
            return sql;
        }

        public SqlText Transform(AddElement ae)
        {
            return null;
        }

        public SqlText Transform(AddTable at)
        {
            // Add to memory the key columns of the table
            keyColumnsPerTable[at.Table] = at.Keys;

            SqlText sql = new SqlText();

            // Determine if we need to add the table
            if (!(columnsPerTable.ContainsKey(at.Table)))
            {
                sql.Command.Append("CREATE TABLE ");
                sql.Command.Append(at.Table);
                sql.Command.Append(" (");
                sql.Command.Append(at.Columns[0]);
                sql.Command.Append(" ");
                sql.Command.Append(at.Domains[0].DBType);
                sql.Command.Append((at.Keys.Contains(at.Columns[0]) && at.EnforcePrimaryKey)
                    ? " NOT NULL"
                    : " NULL");

                for (int i = 1; i < at.Columns.Count; i++)
                {
                    sql.Command.Append(", ");
                    sql.Command.Append(at.Columns[i]);
                    sql.Command.Append(" ");
                    sql.Command.Append(at.Domains[i].DBType);
                    sql.Command.Append((at.Keys.Contains(at.Columns[i]) && at.EnforcePrimaryKey)
                        ? " NOT NULL"
                        : " NULL");
                }

                if (at.EnforcePrimaryKey && at.Keys.Count > 0)
                {
                    sql.Command.Append(", ");
                    sql.Command.Append("CONSTRAINT pk_");
                    sql.Command.Append(at.Table);
                    sql.Command.Append(" PRIMARY KEY CLUSTERED (");
                    sql.Command.Append(String.Join(", ", at.Keys.ToArray()));
                    sql.Command.Append("))");
                }
                else
                {
                    sql.Command.Append(")");
                }

                columnsPerTable[at.Table] = at.Columns;
            }
            else
            {
                List<string> currentColumns = columnsPerTable[at.Table];

                // We need to add the columns individually, if necessary, because we already have the table present
                for (int i = 0; i < at.Columns.Count; i++)
                {
                    if (currentColumns.Contains(at.Columns[i]))
                        continue;

                    // Create the add column statement
                    sql.Command.Append("ALTER TABLE ");
                    sql.Command.Append(at.Table);
                    sql.Command.Append(" ADD ");
                    sql.Command.Append(at.Columns[i]);
                    sql.Command.Append(" ");
                    sql.Command.Append(at.Domains[i].DBType);
                    sql.Command.Append(";");

                    columnsPerTable[at.Table].Add(at.Columns[i]);
                }
                if (!sql.hasNoCommand)
                    // Trim the last semicolon
                    sql.Command.Length--;
            }

            if (sql.hasNoCommand)
            {
                return null;
            }
            return sql;
        }

        public SqlText Transform(AlterColumnRename acr)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("EXEC sp_rename '");
            sql.Command.Append(acr.Table);
            sql.Command.Append(".");
            sql.Command.Append(acr.OldName);
            sql.Command.Append("', '");
            sql.Command.Append(acr.NewName);
            sql.Command.Append("', 'COLUMN'");
            return sql;
        }

        public SqlText Transform(AlterElementRename aer)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("UPDATE ");
            sql.Command.Append(aer.Table);
            sql.Command.Append(" SET ");
            sql.Command.Append(aer.Column);
            sql.Command.Append("=");
            sql.Command.Append(Common.Wrap(aer.NewName));
            sql.Command.Append(" WHERE ");
            sql.Command.Append(aer.Column);
            sql.Command.Append("=");
            sql.Command.Append(Common.Wrap(aer.OldName));
            return sql;
        }

        public SqlText Transform(AlterTableRename atr)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("EXEC sp_rename '");
            sql.Command.Append(atr.OldName);
            sql.Command.Append("', '");
            sql.Command.Append(atr.NewName);
            sql.Command.Append("'");
            return sql;
        }
        
        public SqlText Transform(AntiSemiJoin asj)
        {
            SqlText st1 = asj.Child1.Visit(this);
            SqlText st2 = asj.Child2.Visit(this);
            SqlText keep, pitch;
            
            // Determine which side of the join is the persistent side
            if (asj.Type == JoinType.LEFT)
            {
                keep = st1;
                pitch = st2;
            }
            else
            {
                keep = st2;
                pitch = st1;
            }

            // Construct the return value
            pitch.ClearProjection();
            if (!pitch.hasNoWhere)
            {
                pitch.Where.Insert(0, "(");
                pitch.Where.Append(") AND ");
            }
            pitch.Where.Append(asj.JoinCondition);

            if (!keep.hasNoWhere)
            {
                keep.Where.Insert(0, "(");
                keep.Where.Append(") AND ");
            }
            keep.Where.Append(asj.JoinCondition);

            keep.Where.Append("NOT EXISTS ");
            keep.Where.Append(pitch.ToString());
            return keep;
        }

        public SqlText Transform(ApplyOp ap)
        {
            if (ap.F is SqlFunctionDefintion)
            {
                List<string> translatedInputs = new List<string>();
                SqlText sql = ap.Child.Visit(this);
                foreach (string s in ap.Input)
                {
                    translatedInputs.Add(sql[s]);
                    sql.Remove(s);
                }

                for (int i = 0; i < ap.Output.Count; i++)
                {
                    sql.Add(ap.Output[i], (ap.F as SqlFunctionDefintion).SqlString(i, translatedInputs.ToArray()));
                }
                return sql;
            }

            throw new Exception("Functions that do not have an Sql Function Definition defined are not currently supported.");
        }

        public SqlText Transform(Check ch)
        {
            return new SqlText();
        }
        
        public SqlText Transform(CrossJoin cj)
        {
            SqlText st1 = cj.Child1.Visit(this);
            SqlText st2 = cj.Child2.Visit(this);

            if (!st1.hasNoFrom && !st2.hasNoFrom)
            {
                st1.From.Insert(0, "(");
                st1.From.Append(" CROSS JOIN ");
                st1.From.Append(st2.From);
                st1.From.Append(")");
            }
            else if (!st2.hasNoFrom)
            {
                st1.From = st2.From;
            }

            // Join the where clauses
            if (!st1.hasNoWhere && !st2.hasNoWhere)
            {
                st1.Where.Insert(0, "(");
                st1.Where.Append(") AND (");
                st1.Where.Append(st2.Where);
                st1.Where.Append(")");
            }
            else if (!st2.hasNoWhere)
            {
                st1.Where = st2.Where;
            }

            foreach (string s in st2.projectAliases)
                if (!(st1.projectAliases.Contains(s)))
                    st1.Add(s, st2[s]);
            return st1;
        }

        public SqlText Transform(Delete d)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("DELETE FROM ");
            sql.Command.Append(d.Table);
            if (d.Conditions.Count == 0)
                return sql;

            // Combine the conditions into a string with AND semantics
            sql.Command.Append(" WHERE ");
            sql.Command.Append(d.Columns[0]);
            sql.Command.Append(d.Conditions[0].ToString());

            for (int i = 1; i < d.Conditions.Count; i++)
            {
                sql.Command.Append(" AND ");
                sql.Command.Append(d.Columns[i]);
                sql.Command.Append(d.Conditions[i].ToString());
            }
            return sql;
        }

        public SqlText Transform(DropColumn dc)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("ALTER TABLE ");
            sql.Command.Append(dc.Table);
            sql.Command.Append(" DROP COLUMN ");
            sql.Command.Append(dc.Column);

            // Update internals
            this.columnsPerTable[dc.Table].Remove(dc.Column);

            return sql;
        }

        public SqlText Transform(DropElement de)
        {
            SqlText sql = new SqlText();
            
            // Determine if we need to set to null or to delete rows
            if (keyColumnsPerTable[de.Table].Contains(de.Column))
            {
                sql.Command.Append("DELETE FROM ");
                sql.Command.Append(de.Table);
                sql.Command.Append(" WHERE ");
                sql.Command.Append(de.Column);
                sql.Command.Append("=");
                sql.Command.Append(Common.Wrap(de.Element));
            }
            else
            {
                sql.Command.Append("UPDATE ");
                sql.Command.Append(de.Table);
                sql.Command.Append(" SET ");
                sql.Command.Append(de.Column);
                sql.Command.Append("=NULL WHERE ");
                sql.Command.Append(de.Column);
                sql.Command.Append("=");
                sql.Command.Append(Common.Wrap(de.Element));
            }
            return sql;
        }

        public SqlText Transform(DropForeignKey dfk)
        {
            foundFK = true;
            return new SqlText();
        }
        
        public SqlText Transform(DropTable dt)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("DROP TABLE ");
            sql.Command.Append(dt.Table);

            // Update internals
            this.keyColumnsPerTable.Remove(dt.Table);
            this.columnsPerTable.Remove(dt.Table);

            return sql;
        }

        public SqlText Transform(Error er)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("DECLARE @count int SELECT @count=count(*) FROM (");
            sql.Command.Append(er.Query.Visit(this).ToString());
            sql.Command.Append(") AS Temp; IF @count>0 raiserror('Failed error check',18,1)");
            return new SqlText();
        }
        
        public SqlText Transform(Except e)
        {
            // Create the except string
            SqlText sql = new SqlText();

            sql.From.Append("(");
            sql.From.Append(e.Child1 is RowConstant
                ? "SELECT " + (e.Child1 as RowConstant).AsColumnText
                : e.Child1.Visit(this).ToString());
            sql.From.Append(" EXCEPT ");
            sql.From.Append(e.Child2 is RowConstant
                ? "SELECT " + (e.Child2 as RowConstant).AsColumnText
                : e.Child2.Visit(this).ToString());
            sql.From.Append(")");

            if (e.alias != null)
            {
                sql.From.Append(" AS ");
                sql.From.Append(e.alias);
            }
            else
            {
                sql.From.Append(" AS temp_");
                sql.From.Append(iCount.ToString());
                iCount++;
            }

            foreach (string s in e.exposedColumns)
                sql.Add(s, "");

            return sql;
        }

        public SqlText Transform(Filter f)
        {
            SqlText st = f.Child.Visit(this);
            if (st.hasNoWhere)
            {
                st.Where = new StringBuilder(f.ConditionString);
            }
            else
            {
                st.Where.Insert(0, "(");
                st.Where.Append(")");
                st.Where.Append(" AND ");
                st.Where.Append(f.ConditionString);
            }
            return st;
        }

        public SqlText Transform(ForeignKey fk)
        {
            foundFK = true;
            //Add to list to process all at the end
            fkWorkList.Add(SerializeTableAndColumns(fk.ChildTable, fk.ChildColumns));

            string tablekey = SerializeTableAndColumns(fk.ChildTable, fk.ChildColumns);
            string conditionkey = SerializeConditions(fk.ChildConditionColumns, fk.ChildConditions);

            if (!ForeignKeyTable.Keys.Contains(tablekey))
            {
                ForeignKeyTable.Add(tablekey, new Dictionary<string, List<ForeignKey>>());
            }

            var conditionDictionary = ForeignKeyTable[tablekey];
            if (conditionDictionary.ContainsKey(conditionkey))
            {
                conditionDictionary[conditionkey].Add(fk);
            }
            else
            {
                List<ForeignKey> newList = new List<ForeignKey>();
                newList.Add(fk);
                conditionDictionary[conditionkey] = newList;
            }

            return null;
        }

        public SqlText Transform(Insert i)
        {
            SqlText sql = new SqlText();
            sql.Command.Append("INSERT INTO ");
            sql.Command.Append(i.Table);
            sql.Command.Append(" (");
            sql.Command.Append(String.Join(",", i.Columns.ToArray()));
            sql.Command.Append(") ");
            sql.Command.Append(i.Child.Visit(this).ToString());
            return sql;
        }

        public SqlText Transform(Intersect i)
        {
            // Create the intersect string
            SqlText sql = new SqlText();
            sql.From.Append("(");
            sql.From.Append(i.Children[0] is RowConstant
                ? "SELECT " + (i.Children[0] as RowConstant).AsColumnText
                : i.Children[0].Visit(this).ToString());

            for (int j = 1; j < i.Children.Count; j++)
            {
                sql.From.Append(" INTERSECT ");
                sql.From.Append(i.Children[j] is RowConstant
                    ? "SELECT " + (i.Children[j] as RowConstant).AsColumnText
                    : i.Children[j].Visit(this).ToString());
            }
            
            sql.From.Append(")");

            if (i.alias != null)
            {
                sql.From.Append(" AS ");
                sql.From.Append(i.alias);
            }
            else
            {
                sql.From.Append(" AS temp_");
                sql.From.Append(iCount.ToString());
                iCount++;
            }

            foreach (string s in i.exposedColumns)
                sql.Add(s, "");

            return sql;
        }

        public SqlText Transform(Join j)
        {
            SqlText st1 = j.Child1.Visit(this);
            SqlText st2 = j.Child2.Visit(this);

            st1.From.Insert(0, "(");
            st1.From.Append(" ");
            st1.From.Append(j.Type.ToString());
            st1.From.Append(" JOIN ");
            st1.From.Append(st2.From);
            st1.From.Append(" ON ");
            st1.From.Append(j.JoinCondition);
            st1.From.Append(") ");
            
            // Join the where clauses
            if (!st1.hasNoWhere && !st2.hasNoWhere)
            {
                st1.Where.Insert(0, "(");
                st1.Where.Append(") AND (");
                st1.Where.Append(st2.Where);
                st1.Where.Append(")");
            }
            else if (!st2.hasNoWhere)
            {
                st1.Where = st2.Where;
            }

            foreach (string s in st2.projectAliases)
                if (!(st1.projectAliases.Contains(s)))
                    st1.Add(s, st2[s]);
            return st1;
        }

        public SqlText Transform(Operator o)
        {
            throw new Exception("Unknown operator of type " + o.GetType().Name + " found.");
        }

        public SqlText Transform(PivotOp po)
        {
            // Transform the child reference
            SqlText sql = po.Child.Visit(this);

            // Wrap the FROM clause in the unpivot clause
            SqlText pivot = new SqlText();

            if (provider == MSSQL_PROVIDER)
            {
                foreach (var k in po.KeyMap)
                {
                    pivot.Add(k.Value, po.NewAlias + "." + k.Key);
                }
                foreach (var k in po.FoldValues.Keys)
                {
                    pivot.Add(po.FoldValues[k], "[" + k + "]");
                }

                pivot.From.Append("(");
                pivot.From.Append(sql.ToString());
                pivot.From.Append(") AS ");
                pivot.From.Append(po.NewAlias);
                pivot.From.Append(" PIVOT (max(");
                pivot.From.Append(po.NewAlias);
                pivot.From.Append(".");
                pivot.From.Append(po.ValCol);
                pivot.From.Append(") FOR ");
                pivot.From.Append(po.NewAlias);
                pivot.From.Append(".");
                pivot.From.Append(po.AttCol);
                pivot.From.Append(" IN ");
                pivot.From.Append(po.PivotValueList);
                pivot.From.Append(") AS ");
                pivot.From.Append(po.NewAlias);
            }
            else if (provider == MYSQL_PROVIDER)
            {
                /*  note: pivot table should be child query
                select u1.id, value as Name, u2.city, u3.country
                from pivot_table as u1
                left join (
                    select id, value as 'City'
                    from pivot_table
                    where attribute = 'City'
                ) as u2
                on u1.id = u2.id 
                left join (
                    select id, value as 'Country'
                    from pivot_table
                    where attribute = 'country'
                ) as u3
                on u1.id = u3.id 
                where u1.attribute = 'Name';
                */
                pivot.IsDistinct = true;
                foreach (var k in po.KeyMap)
                {
                    pivot.Add(k.Value, po.NewAlias + "." + k.Key);
                }
                int i = 0;
                // get table name
                SqlText childSQL = po.Child.Visit(this);

                foreach (var k in po.FoldValues.Keys)
                {
                    i++;
                    pivot.Add(po.FoldValues[k], "U_0" + i + "." + k);
                }
                i = 0;
                pivot.From.Append("(" + childSQL.ToString() + ") AS " + po.NewAlias + "\r\n");
                foreach (var k in po.FoldValues.Keys)
                {
                    i++;
                    pivot.From.Append("LEFT JOIN (\r\n");
                    pivot.From.Append("SELECT ");
                    foreach (var j in po.KeyMap)
                    {
                        pivot.From.Append(j.Value + ", ");
                    }
                    pivot.From.Append(po.ValCol + " AS " + k + "\r\n");
                    pivot.From.Append("FROM(" + childSQL.ToString() + ") " + po.NewAlias + "\r\n");
                    pivot.From.Append("WHERE " + po.AttCol + " = '" + k + "'\r\n");
                    pivot.From.Append(") AS " + "U_0" + i + "\r\n");
                    pivot.From.Append("ON ");
                    int numOfKey = 0;
                    foreach (var token in po.KeyMap)
                    {
                        numOfKey++;
                        if (numOfKey > 1)
                            pivot.From.Append(" AND ");
                        // u1.id = u2.id 
                        pivot.From.Append(po.NewAlias + "." + token.Key + " = " + "U_0" + i + "." + token.Key);
                    }
                    pivot.From.Append("\r\n");
                }
            }
            else if (provider == ACCESS_PROVIDER)
            {
                /*
                TRANSFORM Max(D_0.value)
                SELECT D_0.id
                FROM (SELECT * FROM pivot_table)  AS D_0
                GROUP BY D_0.id
                PIVOT D_0.attribute;
                 * */
                // expect to have one key
                SqlText childSQL = po.Child.Visit(this);
                childSQL.useSquareParaAlias = true;
                pivot.IsDistinct = true;
                pivot.useSquareParaAlias = true;

                pivot.Command.Append("TRANSFORM Max(").Append(po.NewAlias).Append(".").Append(po.ValCol).Append(")\r\n");
                pivot.Command.Append("SELECT ");
                int i = 0;
                foreach (var k in po.KeyMap)
                {
                    if (i > 1)
                        pivot.Command.Append(", ");
                    pivot.Command.Append(po.NewAlias).Append(".").Append(k.Key);
                    i++;
                }
                pivot.Command.Append("\r\nFROM (").Append(childSQL.ToString()).Append(") AS ").Append(po.NewAlias);
                pivot.Command.Append("\r\nGROUP BY ");
                i = 0;
                foreach (var k in po.KeyMap)
                {
                    if (i > 1)
                        pivot.Command.Append(", ");
                    pivot.Command.Append(po.NewAlias).Append(".").Append(k.Key);
                    i++;
                }
                pivot.Command.Append("\r\nPIVOT ").Append(po.NewAlias).Append(".").Append(po.AttCol);
            }
            else if (provider == ORACLE_PROVIDER)
            {
                PivotOp.IsOracle = true;
                foreach (var k in po.KeyMap)
                {
                    pivot.Add(k.Value, k.Key);
                }
                foreach (var k in po.FoldValues.Keys)
                {
                    pivot.Add(po.FoldValues[k], k);
                }

                pivot.From.Append("(");
                pivot.From.Append(sql.ToString());
                pivot.From.Append(") ");
                pivot.From.Append(" PIVOT (max(");
                pivot.From.Append(po.ValCol);
                pivot.From.Append(") FOR ");
                pivot.From.Append(po.AttCol);
                pivot.From.Append(" IN ");
                pivot.From.Append(po.PivotValueList);
                pivot.From.Append(") ");
            }
            else if (provider == POSTGRES_PROVIDER)
            {
                /*
                 SELECT *
                 FROM crosstab(
                  'select id, attribute, value
                   from pivot_table
                   order by 1',
                   'select distinct attribute 
                   from pivot_table')
                  AS ct(id integer, Country text, City text, Name text);
                 * */


                pivot.From.Append("crosstab('");
                pivot.From.Append("SELECT ");
                foreach (var k in po.KeyMap)
                {
                    pivot.From.Append(k.Value + ", ");
                }
                pivot.From.Append(po.AttCol + ", ").Append(po.ValCol).Append("\r\n");
                pivot.From.Append(" FROM (").Append(sql.ToString().Replace("'", "''")).Append(") ").Append(po.NewAlias).Append("\r\n");
                pivot.From.Append("ORDER BY 1',\r\n");
                pivot.From.Append("'SELECT DISTINCT ").Append(po.AttCol).Append("\r\n");
                pivot.From.Append(" FROM (").Append(sql.ToString().Replace("'", "''")).Append(") ").Append(po.NewAlias).Append("')").Append("\r\n");
                pivot.From.Append(" AS ").Append(po.NewAlias).Append("(");
                int i = 0;
                foreach (var k in po.KeyMap)
                {
                    if (i != 0)
                        pivot.From.Append(", ");
                    pivot.From.Append(k.Value + " integer");
                }
                foreach (var k in po.FoldValues.Keys)
                {
                    pivot.From.Append(", ").Append(k).Append(" text");
                }
                pivot.From.Append(")");
            }

            return pivot;
        }

        public SqlText Transform(Project p)
        {
            SqlText st = p.Child.Visit(this);
            List<string> toDelete = new List<string>();
            
            // Build a list of all of the columns that need to be dropped
            foreach (string s in st.projectAliases)
                if (!(p.toProject.Contains(s)))
                    toDelete.Add(s);
            
            // Drop the columns
            foreach (string s in toDelete)
                st.Remove(s);
            foreach (string s in p.toProject)
            {
                if (!st.projectAliases.Contains(s)) st.Add(s,s);
            }
            return st;
        }

        public SqlText Transform(RowConstant rc)
        {
            SqlText sql = new SqlText();
            
            for (int i = 0; i < rc.exposedColumns.Count; i++)
            {
                sql.Add(rc.exposedColumns[i], Common.Wrap(rc.Fields[i]));
            }
            
            return sql;
        }
        
        public SqlText Transform(TableRef tr)
        {
            SqlText st = new SqlText();
            st.From.Append(tr.Table);
            st.From.Append(" AS ");
            st.From.Append(tr.Alias);
            
            for (int i = 0; i < tr.Columns.Count; i++)
            {
                st.Add(tr.exposedColumns[i], tr.Alias + "." + tr.Columns[i]);
            }

            return st;
        }

        public SqlText Transform(Union u)
        {
            // Create the union string
            SqlText sql = new SqlText();
            sql.From.Append("(");
            sql.From.Append(u.Children[0] is RowConstant
                ? "SELECT " + (u.Children[0] as RowConstant).AsColumnText
                : u.Children[0].Visit(this).ToString());

            for (int i = 1; i < u.Children.Count; i++)
            {
                sql.From.Append(" UNION ");
                sql.From.Append(u.Children[i] is RowConstant
                    ? "SELECT " + (u.Children[i] as RowConstant).AsColumnText
                    : u.Children[i].Visit(this).ToString());
            
            }
            sql.From.Append(")");

            if (u.alias != null)
            {
                sql.From.Append(" AS ");
                sql.From.Append(u.alias);
            }
            else
            {
                sql.From.Append(" AS temp_");
                sql.From.Append(iCount.ToString());
                iCount++;
            }

            foreach (string s in u.exposedColumns)
            {
                sql.Add(s, "");
            }

            return sql;
        }

        public SqlText Transform(UnpivotOp uo)
        {
            SqlText sql = null;
            if (provider == MSSQL_PROVIDER)
            {
                /*      Note: Inside the INNER FORM, is child transform
                 *  SELECT ID, att, val
                    FROM
                    (SELECT ID,
                    Convert(sql_variant, T_0_Name) AS DName,
                    Convert(sql_variant, T_0_Height) AS DHeight,
                    Convert(sql_variant, T_0_Gender) AS DGender
                    FROM (SELECT D_0.ID AS 'ID', D_0.Name AS 'T_0_Name', D_0.Height AS 'T_0_Height', D_0.Gender AS 'T_0_Gender' 
                    FROM Patient AS D_0) D_0) D_0
                    UNPIVOT
                    (val FOR att IN (DName, DHeight, DGender)) AS unpvtD_0

                */
                sql = new SqlText();
                string id = uo.FoldColumns[0];

                // get the last transform
                // SELECT D_0.ID AS 'ID', D_0.Name AS 'T_0_Name', D_0.Height AS 'T_0_Height', D_0.Gender AS 'T_0_Gender' 
                // FROM Patient AS D_0
                SqlText childSQL = uo.Child.Visit(this);

                List<String> cols = uo.Child.exposedColumns;

                sql.Command.Append("SELECT ");
                sql.Command.Append(String.Join(", ", uo.FoldColumns.ToArray()));
                sql.Command.Append("\r\nFROM\r\n");
                sql.Command.Append("(SELECT " + id + ",\r\n");
                int count = 0;
                foreach (string col in cols)
                {
                    count++;
                    if (count == 1)
                        continue;
                    if (count > 2)
                        sql.Command.Append(",\r\n");
                    String originalColName = col.Substring(4); // cut the alias
                    sql.Command.Append("Convert(sql_variant, " + col + ") AS " + originalColName);
                }
                sql.Command.Append("\r\nFROM (" + childSQL.ToString() + ") " + uo.NewAlias + ") " + uo.NewAlias + "\r\n");
                sql.Command.Append("UNPIVOT\r\n");
                sql.Command.Append("(" + uo.ValCol + " FOR " + uo.AttCol + " IN (");
                count = 0;
                foreach (string col in cols)
                {
                    count++;
                    if (count == 1)
                        continue;
                    if (count > 2)
                        sql.Command.Append(", ");
                    String originalColName = col.Substring(4); // cut the alias
                    sql.Command.Append(originalColName);
                }
                sql.Command.Append(")) AS unpvt" + uo.NewAlias);
            }
            else if (provider == ACCESS_PROVIDER || provider == MYSQL_PROVIDER)     //  same query for both access and mysql
            {
                sql = new SqlText();
                List<String> cols = uo.Child.exposedColumns;

                if (cols.Count == 0)
                    throw new Exception("There is no columns in the table");
                string id = uo.FoldColumns[0];

                // get table name
                SqlText childSQL = uo.Child.Visit(this);

                int count = 0;
                foreach (string col in cols)
                {
                    count++;
                    if (count > 2)
                        sql.Command.Append("UNION ALL\r\n");
                    else if (count == 1)
                        continue;
                    String originalColName = col.Substring(4); // cut the alias
                    if (provider == MYSQL_PROVIDER)
                    {
                        sql.Command.Append("SELECT " + id + ", '" + originalColName + "' AS '" + uo.AttCol + "', ");
                        sql.Command.Append(col + " AS '" + uo.ValCol + "' \r\n");
                        sql.Command.Append("FROM(" + childSQL.ToString() + ") " + uo.NewAlias + "\r\n");
                        sql.Command.Append("WHERE " + col + " IS NOT NULL\r\n");
                    }
                    else if (provider == ACCESS_PROVIDER)
                    {
                        sql.Command.Append("SELECT " + id + ", '" + originalColName + "' AS [" + uo.AttCol + "], ");
                        sql.Command.Append(col + " AS [" + uo.ValCol + "] \r\n");
                        sql.Command.Append("FROM(" + childSQL.ToString() + ") " + uo.NewAlias + "\r\n");
                        sql.Command.Append("WHERE " + col + " IS NOT NULL\r\n");
                    }
                }
                sql.Command.Append("ORDER BY " + id);
            }
            else if (provider == ORACLE_PROVIDER)
            {
                sql = new SqlText();
                string id = uo.FoldColumns[0];

                // get the last transform
                // SELECT D_0.ID AS 'ID', D_0.Name AS 'T_0_Name', D_0.Height AS 'T_0_Height', D_0.Gender AS 'T_0_Gender' 
                // FROM Patient AS D_0
                SqlText childSQL = uo.Child.Visit(this);

                List<String> cols = uo.Child.exposedColumns;

                sql.Command.Append("SELECT *");
                // sql.Command.Append(String.Join(", ", uo.FoldColumns.ToArray()));
                sql.Command.Append("\r\nFROM\r\n");
                sql.Command.Append("(SELECT " + id + ",\r\n");
                int count = 0;
                foreach (string col in cols)
                {
                    count++;
                    if (count == 1)
                        continue;
                    if (count > 2)
                        sql.Command.Append(",\r\n");
                    sql.Command.Append("TO_CHAR(" + col + ") AS " + col);
                }
                sql.Command.Append("\r\nFROM (" + childSQL.ToString() + ") " + uo.NewAlias + ") " + uo.NewAlias + "\r\n");
                sql.Command.Append("UNPIVOT\r\n");
                sql.Command.Append("(" + uo.ValCol + " FOR " + uo.AttCol + " IN (");
                count = 0;
                foreach (string col in cols)
                {
                    count++;
                    if (count == 1)
                        continue;
                    if (count > 2)
                        sql.Command.Append(", ");
                    sql.Command.Append(col);
                }
                sql.Command.Append(")) unpvt" + uo.NewAlias);
            }
            else if (provider == POSTGRES_PROVIDER)
            {
                /*
                SELECT * FROM unpivot('select * from patient', 'id', '{"height", "name", "gender"}'::text[]) 
                AS D_0(id text, attribute text, value text) ORDER BY id;
                 */
                sql = new SqlText();
                SqlText childSQL = uo.Child.Visit(this);
                List<String> cols = uo.Child.exposedColumns;
                string id = uo.FoldColumns[0];
                int count = 0;

                sql.Command.Append("SELECT * FROM unpivot('");
                sql.Command.Append(childSQL.ToString().Replace("'", "''"));
                sql.Command.Append("', '").Append(uo.NewAlias);
                sql.Command.Append("', '").Append(id).Append("', '{");
                // column name with alias
                // TableRef must have alias t_0_name
                foreach (string col in cols)
                {
                    count++;
                    if (count == 1)
                        continue;
                    if (count > 2)
                        sql.Command.Append(", ");
                    if (uo.Child is TableRef)
                        sql.Command.Append("\"").Append(col).Append("\"");
                    else
                    {
                        String originalColName = col.Substring(4);
                        sql.Command.Append("\"").Append(originalColName).Append("\"");
                    }
                }
                sql.Command.Append("}'::text[], '{");

                // add original column name
                count = 0;
                foreach (string col in cols)
                {
                    count++;
                    if (count == 1)
                        continue;
                    if (count > 2)
                        sql.Command.Append(", ");
                    String originalColName = col.Substring(4); // cut the alias
                    sql.Command.Append("\"").Append(originalColName).Append("\"");
                }
                sql.Command.Append("}'::text[])").Append("\r\n");
                sql.Command.Append("AS ").Append(uo.NewAlias).Append("(");
                sql.Command.Append(id).Append(" text, ").Append(uo.AttCol).Append(" text, ");
                sql.Command.Append(uo.ValCol).Append(" text)").Append("\r\n");
                sql.Command.Append("ORDER BY ").Append(id);
            }
            
            return sql;
        }

        public SqlText Transform(Update up)
        {
            if (up.Columns.Count <= 0)
                return null;

            SqlText sql = up.Child.Visit(this);
            SqlText sqlCommand = new SqlText();

            // Two situations: update has self-join or not
            // If there is a self-join (to pick up on key values), we need to pick up the join attributes and join table
            if (up.needJoin)
            {
                sql.From.Insert(0, " LEFT JOIN ");
                sql.From.Insert(0, up.Table);
                sql.From.Append(" ON ");
                sql.From.Append(up.JoinString);

                if (sql.hasNoWhere && up.ConditionString != "")
                {
                    sql.Where = new StringBuilder(up.ConditionString);
                }
                else if (up.ConditionString != "")
                {
                    sql.Where.Insert(0, "(");
                    sql.Where.Append(")");
                    sql.Where.Append(" AND (");
                    sql.Where.Append(up.ConditionString);
                    sql.Where.Append(")");
                }
            }
            else if (up.ConditionString != "")
            {
                sql.Where = new StringBuilder(up.ConditionString);
            }

            sqlCommand.Command = new StringBuilder(sql.ToUpdateString(up.Table, up.Columns));
            return sqlCommand;
        }

        public List<SemanticUnit> SemanticUnits(string table)
        {
            return new List<SemanticUnit>();
        }

        #endregion
    }
}